#! /usr/bin/python

import MySQLdb

class DBConnection:
    def __init__(self, host, user, passwd, database):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.database = database
        self.db = MySQLdb.connect(host, user, passwd)

    def closeConn(self):
        self.db.close()

    def add_item_to_cadouri_table(self, product):
        print "Adding products to db"
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "INSERT INTO cadouri(nume, poza, descriere, link, id_cat)"
        sql += " VALUES ('%s', '%s', '%s', '%s', %s)" %(
                product.p_name, product.p_pict, product.p_desc,
                product.p_link, product.p_cat_id)
        print "SQL = " + str(sql)

        try:
            cursor.execute(sql)
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from add_item_to_cadouri_table!!"
            import pdb; pdb.set_trace()
            self.db.rollback()

    def add_item_to_gift_attrs_table(self, attribute):
        print "Adding attribute to db"
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "INSERT INTO atribute_cadouri(id_atribut, id_cadou, valoare)"
        sql += " VALUES ('%s', '%s', '%s')" %(
                attribute.attr_id, attribute.gift_id, attribute.value
                )
        print "SQL = " + str(sql)

        try:
            cursor.execute(sql)
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from add_item_to_cadouri_table!!"
            import pdb; pdb.set_trace()
            self.db.rollback() 


    def get_cat_id(self, cat_name):
        """
        """
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "SELECT id FROM categorii WHERE nume='%s'"%(cat_name)
        print sql

        try:
            cursor.execute(sql)
            rows = cursor.fetchall()
            id = int(rows[0][0])
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from get_cat_id!!"
            self.db.rollback()

        return id

    def get_attr_id(self, attr_name):
        """
        """
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "SELECT id FROM atribute WHERE nume='%s'"%(attr_name)
        print sql

        try:
            cursor.execute(sql)
            rows = cursor.fetchall()
            id = int(rows[0][0])
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from get_attr_id!!"
            self.db.rollback()

        return id

    def get_last_prod_id(self):
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "SELECT max(id) FROM cadouri"
        print sql

        try:
            cursor.execute(sql)
            rows = cursor.fetchall()
            id = int(rows[0][0])
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from get_last_prod_id!!"
            self.db.rollback()
            id = 0

        return id 

    def get_all_attrs(self):
        """
        """
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "SELECT id, nume FROM atribute"
        attrs_dict = {}
        print sql

        try:
            cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                a_id = int(row[0])
                a_name = row[1]
                attrs_dict[a_name]=a_id
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from get_all_attrs!!"
            self.db.rollback()

        return attrs_dict


    def get_prod_id(self, pname):
        """
        """
        cursor = self.db.cursor()
        self.db.select_db('sac_recomandari')
        sql = "SELECT id FROM cadouri WHERE nume='%s'"%(pname)
#        print sql

        try:
            cursor.execute(sql)
            rows = cursor.fetchall()
            id = int(rows[0][0])
        except:
            print "#EMA# OOPS --> Couldn't execute SQL Querry from get_attr_id!!"
            self.db.rollback()

        return id 

